package com.hethongviet.preventingmachine.helps.db;

import java.util.ArrayList;
import java.util.List;

import com.hethongviet.preventingmachine.Utils;
import com.hethongviet.preventingmachine.models.Number;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class NumberSQLite
{
	public static final String TABLE_NAME = "numbers";

	public static final String COLUMN_ID_DB = "idDB";

	public static final String COLUMN_DEVICE_NUMBER = "number";
	public static final String COLUMN_DEVICE_NAME = "name";
	public static final String COLUMN_IS_CONTROL = "is_controlled";
	public static final String COLUMN_IS_ALARM = "is_alarmed";
	public static final String COLUMN_STATUS_SYNCHRONIZED = "is_synchronized";
	public static final String COLUMN_IS_ACTIVE = "is_active";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;
	private String[] _allColumns = { COLUMN_ID_DB,

			COLUMN_DEVICE_NUMBER, COLUMN_DEVICE_NAME, COLUMN_IS_ALARM, COLUMN_IS_CONTROL,
			COLUMN_STATUS_SYNCHRONIZED, COLUMN_IS_ACTIVE };

	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "

+ COLUMN_DEVICE_NUMBER + " text, " + COLUMN_DEVICE_NAME + " text, " + COLUMN_IS_ALARM + " integer, "
			+ COLUMN_IS_CONTROL + " integer, " + COLUMN_STATUS_SYNCHRONIZED
			+ " integer, " + COLUMN_IS_ACTIVE + " integer";

	public NumberSQLite(Context context, String databaseName)
	{
		_dbHelper = new MySQLiteHelper(context, null, null, databaseName);
	}

	public void open() throws SQLException
	{
		_database = _dbHelper.getWritableDatabase();
	}

	public void close()
	{
		_dbHelper.close();
	}

	private boolean dataExist(Number number, Cursor cursor)
	{
		if (cursor == null)
		{
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		{
			long idDB = cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB));
			if (idDB == number.getIdDB()) return true;
		}
		return false;
	}

	/**
	 * @param number
	 * @return row id -1: insert fail
	 */
	public long insert(Number number)
	{
		Utils.debug("insert -- " + TABLE_NAME + " : admin");
		open();

		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID_DB + " = " + number.getIdDB(), null);

		long idInsert = -1;
		if (!dataExist(number, cursor))
		{
			Utils.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery("INSERT INTO "
					+ TABLE_NAME + "(" + COLUMN_DEVICE_NUMBER + ", " + COLUMN_DEVICE_NAME + ", "
					+ COLUMN_IS_ALARM + ", " + COLUMN_IS_CONTROL + ", "
					+ COLUMN_STATUS_SYNCHRONIZED + ", " + COLUMN_IS_ACTIVE
					+ ") values( ?, ?, ?, ?, ?, ?)",

			new String[] { number.getNumber(), number.getName(), number.isAlarm() ? "1" : "0",
					number.isControl() ? "1" : "0",
					number.getStatusSynchronize() + "",
					number.isActive() ? "1" : "0" });

			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast()) idInsert = cursorInsert.getLong(cursorInsert.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		}
		else
		{
			Utils.debug("insert 1111111111111 update");
			ContentValues contentValues = new ContentValues();
			contentValues.put(COLUMN_DEVICE_NUMBER, number.getNumber());
			contentValues.put(COLUMN_DEVICE_NAME, number.getName());
			contentValues.put(COLUMN_IS_ALARM, number.isAlarm() ? "1" : "0");
			contentValues.put(COLUMN_IS_CONTROL, number.isControl() ? "1" : "0");
			contentValues.put(COLUMN_STATUS_SYNCHRONIZED,
					number.getStatusSynchronize() + "");
			contentValues.put(COLUMN_IS_ACTIVE, number.isActive() ? "1" : "0");

			_database.update(TABLE_NAME, contentValues, COLUMN_ID_DB + " = "
					+ number.getIdDB(), null);

			idInsert = number.getIdDB();
		}
		cursor.close();
		close();
		return idInsert;
	}

	public void deleteAll()
	{
		open();

		System.out.println("Deleted all rows of table " + TABLE_NAME);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public void delete(Number number)
	{
		open();

		System.out.println("Deleted row of table " + TABLE_NAME + " id = "
				+ number.getIdDB());
		_database.delete(TABLE_NAME, COLUMN_ID_DB + " = " + number.getIdDB(),
				null);

		close();
	}

	public List<Number> getAll()
	{
		open();

		List<Number> listClients = new ArrayList<Number>();

		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			Number number = cursorToNumber(cursor);
			listClients.add(number);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return listClients;
	}

	private Number cursorToNumber(Cursor cursor)
	{
		Number number = new Number();
		number.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));
		number.setNumber(cursor.getString(cursor.getColumnIndex(COLUMN_DEVICE_NUMBER)));
		number.setName(cursor.getString(cursor.getColumnIndex(COLUMN_DEVICE_NAME)));
		number.setAlarm(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_ALARM)) == 1);
		number.setControl(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_CONTROL)) == 1);
		number.setStatusSynchronize(cursor.getInt(cursor.getColumnIndex(COLUMN_STATUS_SYNCHRONIZED)));
		number.setActive(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_ACTIVE)) == 1);
		return number;
	}

	public final boolean isTableExist()
	{
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery(
				"select DISTINCT tbl_name from sqlite_master where tbl_name = '"
						+ TABLE_NAME + "'", null);
		isExist = cursor != null && cursor.getCount() > 0;

		cursor.close();
		close();

		return isExist;
	}
}
